package DAO;

import entity.User;
import utils.DBHelper;
import utils.MD5;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;


public class UserDao {

    DBHelper dbHelper = new DBHelper();

    public User queryLogin(String username,String password) {

        Connection connection = dbHelper.getConnection();
        PreparedStatement pst = null;

        String passwordMD5 = MD5.md5(password);

        try {
            pst = connection.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
            pst.setObject(1,username);
            pst.setObject(2,passwordMD5);
            ResultSet rs = pst.executeQuery();
            rs.next();
            String name = rs.getString("name");
            String email = rs.getString("email");
            int score = rs.getInt("score");

            User user = new User(username,name,password,email,score);

            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbHelper.close(connection,pst);
        }
        return null;
    }

    public User queryRegister(String username,String name,String password,String email,int score) {

        Connection connection = dbHelper.getConnection();
        PreparedStatement pst = null;

        String passwordMD5 = MD5.md5(password);

        try {
            pst = connection.prepareStatement("INSERT INTO `user` (`username`, `name`, `password`, `email`,`score`) VALUES (?, ?, ?, ?, ?)");
            pst.setObject(1,username);
            pst.setObject(2,name);
            pst.setObject(3,passwordMD5);
            pst.setObject(4,email);
            pst.setObject(5,score);
            int rs = pst.executeUpdate();

            if (rs == 1) {
                User user = new User(username,name,password,email,score);
                return user;
            }else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbHelper.close(connection,pst);
        }
        return null;
    }

    public User queryByUsername(String username) {

        Connection connection = dbHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM user WHERE username = ?");
            pst.setObject(1,username);

            ResultSet rs = pst.executeQuery();

            rs.next();
            String name = rs.getString("name");
            String email = rs.getString("email");
            int score = rs.getInt("score");

            User user = new User(username,name,null,email,score);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbHelper.close(connection,pst);
        }
        return null;
    }

    public List<User> queryByScoreSort() {

        Connection connection = dbHelper.getConnection();
        PreparedStatement pst = null;
        List<User> userList = new ArrayList<User>();

        try {
            pst = connection.prepareStatement("SELECT * FROM user");

            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                String username = rs.getString("username");
                String name = rs.getString("name");
                String email = rs.getString("email");
                int score = rs.getInt("score");

                User user = new User(username,name,null,email,score);
                userList.add(user);
            }
            Collections.sort(userList);

            for (User user:userList){
                System.out.println(user);
            }
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbHelper.close(connection,pst);
        }
        return null;
    }

    public Boolean deleteByUsername(String username) {

        Connection connection = dbHelper.getConnection();
        PreparedStatement pst = null;

        try {
            System.out.println("test test test");
            pst = connection.prepareStatement("DELETE FROM user WHERE username = ?");

            pst.setObject(1,username);

            int rs = pst.executeUpdate();

            if (rs == 1) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbHelper.close(connection,pst);
        }
        return false;
    }

//    public static void main(String[] args) {
//        new UserDao().queryByScoreSort();
//    }

}
